﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.ReportService.Order
{
    public class GetOrderByIdDbCommand : ICommand<OrderDTO>
    {
        private readonly Guid _orderId;
        private const string _sql = @"
                    SELECT o.*, u.LastName, u.FirstName
                    FROM dbo.[Order] o LEFT JOIN dbo.[UserProfile] u ON o.UserId = u.UserId
                    WHERE OrderId = @OrderId
        ";

        public GetOrderByIdDbCommand(Guid orderId)
        {
            _orderId = orderId;
        }

        public OrderDTO Execute()
        {
            var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            using (var connection = new SqlConnection(connectionString))
            {
                OrderDTO item = null;
                int totalCount = 0;
                var cmd = new SqlCommand(_sql, connection);
                cmd.Parameters.Add("@OrderId", SqlDbType.UniqueIdentifier).Value = _orderId;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        item = GetDto(reader);
                        break;
                    }
                }
                finally
                {
                    reader.Close();
                }
                return item;
            }
        }

        private OrderDTO GetDto(SqlDataReader reader)
        {
            var order = new OrderDTO()
            {
                OrderId = (Guid)reader["OrderId"],
                Email = reader["Email"].ToString(),
                FirstName = reader["FirstName"].ToString(),
                LastName = reader["LastName"].ToString(),
            };
            order.SubTotal = reader.GetDecimal(reader.GetOrdinal("SubTotal"));
            order.CreationDate = reader.GetDateTime(reader.GetOrdinal("CreationDate"));
            order.Status = reader.GetInt32(reader.GetOrdinal("Status"));
            order.BillingAddress = reader["BillingAddress"] != DBNull.Value ? reader.GetString(reader.GetOrdinal("BillingAddress")) : string.Empty;
            order.BillingCity = reader["BillingCity"] != DBNull.Value ? reader.GetString(reader.GetOrdinal("BillingCity")) : string.Empty;
            order.BillingCountry = reader["BillingCountry"] != DBNull.Value ? reader.GetString(reader.GetOrdinal("BillingCountry")) : string.Empty;
            order.BillingZipCode = reader["BillingZipCode"] != DBNull.Value ? reader.GetString(reader.GetOrdinal("BillingZipCode")) : string.Empty;
            order.TransactionNo = reader["TransactionNo"] != DBNull.Value ? reader.GetString(reader.GetOrdinal("TransactionNo")) : string.Empty;
            order.OrderUniqueId = reader["OrderUniqueId"] != DBNull.Value ? reader.GetInt32(reader.GetOrdinal("OrderUniqueId")) : 0;
            return order;
        }
    
    }
}
